package cn.mapway.tools.db;

import org.nutz.lang.Lang;
import schemacrawler.schema.*;

import java.util.Collection;

public class DbTools {

    public static String toCreateViewScript(View table, IConfigure configure) {


        StringBuilder script = new StringBuilder();
        script.append("-- view " + table.getName() + "--\r\n");
        script.append("CREATE VIEW \"" + table.getSchema().getName() + "\".\"" + table.getName() + "\" AS \r\n");
        script.append(table.getDefinition());
        script.append("\r\n");
        script.append("\r\n");

        script.append(String.format("ALTER TABLE \"%1$s\".\"%2$s\" OWNER TO \"%3$s\";\r\n",
                table.getSchema().getName(),
                table.getName(),
                configure.getUser()
        ));
        return script.toString();
    }


    /**
     * CREATE TABLE "public"."cp_user_product" (
     * "id" VARCHAR ( 64 ) COLLATE "pg_catalog"."default" NOT NULL,
     * "user_id" int8,
     * "user_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
     * "product_id" VARCHAR ( 64 ) COLLATE "pg_catalog"."default",
     * "create_time" TIMESTAMP ( 6 ),
     * "expire_time" TIMESTAMP ( 6 ),
     * "region_id" int4,
     * "region_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
     * "symbol_style" json,
     * "role" int4,
     * "layers" json,
     * CONSTRAINT "user_product_pkey" PRIMARY KEY ( "id" )
     * );
     * ALTER TABLE "public"."cp_user_product" OWNER TO "cangling";
     * COMMENT ON COLUMN "public"."cp_user_product"."symbol_style" IS 'type:cn.mapway.geo.client.style.SymbolStyle';
     * COMMENT ON COLUMN "public"."cp_user_product"."layers" IS 'list:cn.satway.cis.common.db.custom.ProductLayer';
     * COMMENT ON TABLE "public"."cp_user_product" IS '用户关联的产品表';
     *
     * @param table
     * @return
     */
    public static String toCreateScript(Table table, IConfigure configure) {
        StringBuilder script = new StringBuilder();

        script.append(
                String.format("CREATE TABLE \"%1$s\".\"%2$s\" (\r\n", table.getSchema().getName(), table.getName()
                ));
        int index = 0;
        for (Column column : table.getColumns()) {
            script.append(toColumnDefine(column));
            if (index < table.getColumns().size() - 1) {
                script.append(",\r\n");
            }
            index++;
        }

        Collection<TableConstraint> tableConstraints = table.getTableConstraints();
        if (tableConstraints != null && tableConstraints.size() > 0) {
            script.append(",\r\n");
            int j = 0;
            for (TableConstraint constraint : tableConstraints) {
                TableConstraintType type = constraint.getType();
                boolean hasline = false;
                switch (type) {
                    case check:
                        if (!constraint.getDefinition().toLowerCase().endsWith("is not null")) {
                            hasline = true;
                            script.append("\tCONSTRAINT \"" + constraint.getName() + "\" " + type.getValue() + " (" + constraint.getDefinition() + ")");
                        }
                        break;
                    case primary_key:
                    case unique:
                        hasline = true;
                        script.append("\tCONSTRAINT \"" + constraint.getName() + "\" " + type.getValue() + " " + toConstraintColumns(constraint));
                        break;
                    case unknown:
                    default:
                }

                if (hasline && j < tableConstraints.size() - 1) {
                    script.append(" ,\r\n");
                }
                j++;
            }
            script.setLength(script.length() - 3);
        } else {
            script.append("\r\n");
        }
        script.append("\r\n);");

        script.append("\r\n");
        script.append(String.format("ALTER TABLE \"%1$s\".\"%2$s\" OWNER TO \"%3$s\";\r\n",
                table.getSchema().getName(), table.getName(), configure.getUser()
        ));

        for (Column column : table.getColumns()) {
            script.append(String.format("COMMENT ON COLUMN \"%1$s\".\"%2$s\".\"%3$s\" IS '%4$s';\r\n",
                    table.getSchema().getName(),
                    table.getName(),
                    column.getName(),
                    column.getRemarks()
            ));
        }
        if (table.getRemarks() != null) {
            script.append(String.format("COMMENT ON TABLE \"%1$s\".\"%2$s\" IS '%3$s';\r\n",
                    table.getSchema().getName(),
                    table.getName(),
                    table.getRemarks()
            ));
        }

        return script.toString();
    }

    private static String toConstraintColumns(TableConstraint constraint) {
        StringBuilder script = new StringBuilder();
        if (constraint.getConstrainedColumns().size() > 0) {
            script.append(" (");
            for (int i = 0; i < constraint.getConstrainedColumns().size(); i++) {
                if (i > 0) {
                    script.append(" , ");
                }
                script.append(constraint.getConstrainedColumns().get(i).getName());
            }
            script.append(")");
        }
        return script.toString();
    }

    private static String wrap(String text) {
        return "\"" + text.trim() + "\" ";
    }

    private static String toColumnDefine(Column column) {

        StringBuilder sb = new StringBuilder(500);
        sb.append("\t");
        sb.append(wrap(column.getName()));
        String dbtype = column.getColumnDataType().getDatabaseSpecificTypeName();
        sb.append(dbtype);

        if (needModifer(dbtype) && column.getSize() > 0) {
            if(column.getSize()<10485700) {
                sb.append(" ( " + column.getSize() + " ) ");
            }
        }
        if (!column.isNullable()) {
            sb.append(" NOT NULL ");
        }
        String temp = dbtype.toLowerCase();
        if (temp.equals("serial") || temp.equals("bigserial")) {

        } else {
            if (column.getDefaultValue() != null) {
                sb.append(" DEFAULT " + column.getDefaultValue());
            }
        }
        return sb.toString();
    }

    private static boolean needModifer(String dbtype) {
        String[] number = new String[]{"int", "bigint", "serial", "bigserial", "bool", "timestamp","time", "float", "double","text","json"};
        String temp = dbtype.toLowerCase();

        for (int i = 0; i < number.length; i++) {
            if (temp.startsWith(number[i])) {
                return false;
            }
        }

        return true;
    }
}
